package com.zhao.mianshi

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

/**
 * Description: <br/>
 * Copyright (c) ，2020 ， 赵 <br/>
 * This program is protected by copyright laws. <br/>
 * Date： 2020/11/16 13:46
 * 需求:编写 sql 实现每个用户截止到每月为止的最大单月访问次数和累计到该月的 总访问次数
 * 数据
 * userid,month,visits
 * A,2015‐01,5
 * A,2015‐01,15
 * B,2015‐01,5
 * A,2015‐01,8
 * B,2015‐01,25
 * A,2015‐01,5
 * A,2015‐02,4
 * A,2015‐02,6
 * B,2015‐02,10
 * B,2015‐02,5
 * A,2015‐03,16
 * A,2015‐03,22
 * B,2015‐03,23
 * B,2015‐03,10
 * B,2015‐03,1
 *
 * @author 柒柒
 * @version : 1.0
 */

object test1 {
  def main(args: Array[String]): Unit = {

    val conf: SparkConf = new SparkConf()
      .setAppName(this.getClass.getName)
      .setMaster("local[*]")


    val spark: SparkSession = SparkSession.builder().config(conf)
      .enableHiveSupport() //默认不支持外部hive,这里需要调用方法
      .getOrCreate()

    spark.sql("use mianshi")

    spark.sql(
      """
        |select userid,month,visits,
        |    max(visits) over(distribute by userid sort by month) max_visits,
        |    sum(visits) over(distribute by userid sort by month) sum_visits
        |from(
        |select userid,month,sum(visits) visits
        |    from mianshi1
        |group by userid,month) t1
        |""".stripMargin).show()
  }
}
